﻿using System.Data;
using SemiCASE.DataAccess;
using SemiCASE.DataType.Common.Attributes;
using System;

namespace SemiCASE.Business
{
    [ServiceConnectionNameAttribute("SemiCASEConnectionString")]
    public class UseCaseBS : BusinessBase
    {
        public DataTable LoadUseCases(Guid pProjectID)
        {
            DataTable dt = new DataTable();
            IData data = GetDataObject();
            string sqlText = @"WITH PROJECT_USECASES AS
                            (
	                            SELECT DISTINCT 
			                            [UseCaseID]
	                            FROM [SemiCaseDB].[dbo].[RELATION]
	                            WHERE ProjectID =  @ProjectID
                            )

                            SELECT 
                                  [UseCaseID]
                                  , Name
                                  , [Scope]
                                  , [Level]
                                  , [PrimaryActor]
                                  , [StakeHolders]
                                  , [Precondition]
                                  , [SuccessGuarantee]
                                  , [SpecialRequirement]
                                  , [Technology]
                                  , [Frequency]
                                  , [Miscelleneous]
                                  , UC.[StatusID]
                                  , UCS.StatusName
                                  , '' AS SiraNo
                                  , CreationDate
                                  , Nonfunctional
                                  , Priority
                            FROM [SemiCaseDB]..[USE_CASE] AS UC 
	                             INNER JOIN SemiCaseDB..USE_CASE_STATUS AS UCS ON UC.StatusID = UCS.StatusID
                            WHERE UseCaseID IN (SELECT * FROM PROJECT_USECASES)
                            ORDER BY UC.CreationDate";

            data.AddSqlParameter("ProjectID", pProjectID, SqlDbType.UniqueIdentifier);
            data.GetRecords(dt, sqlText);
            return dt;
        }

        public DataTable LoadStatus()
        {
            DataTable dt = new DataTable();
            IData data = GetDataObject();
            string sqlText = @"SELECT 
                                    [StatusID]
                                    , [StatusName]
                               FROM [SemiCaseDB].[dbo].[USE_CASE_STATUS]";

            data.GetRecords(dt, sqlText);
            return dt;
        }

        public void CreateUseCase(Guid pUseCaseID,
                                    Guid pProjectID,
                                    string pFrequency,
                                    string pLevel,
                                    string pMisc,
                                    string pPrecondition,
                                    string pPrimaryActor,
                                    string pScope,
                                    string pSpecilaRequirements,
                                    string pStakeHolders,
                                    string pSuccess,
                                    string pTechnology,
                                    string pUseCaseName,
                                    int pStatusID,
                                    DataTable dtMss,
                                    DataTable dtExtention,
                                    DataTable dtExtentionSteps,
                                    DateTime pCreationDate,
                                    string pPriority)
        {
            IData data = GetDataObject();
            string sqlText = @"INSERT INTO [SemiCaseDB].[dbo].[USE_CASE]
                                       ([UseCaseID]
                                       ,[Name]
                                       ,[Scope]
                                       ,[Level]
                                       ,[PrimaryActor]
                                       ,[StakeHolders]
                                       ,[Precondition]
                                       ,[SuccessGuarantee]
                                       ,[SpecialRequirement]
                                       ,[Technology]
                                       ,[Frequency]
                                       ,[Miscelleneous]
                                       ,[StatusID]
                                       ,CreationDate
                                       ,Nonfunctional
                                       ,Priority)
                                 VALUES
                                       (@UseCaseID
                                       ,@Name 
                                       ,@Scope 
                                       ,@Level
                                       ,@PrimaryActor
                                       ,@StakeHolders
                                       ,@Precondition
                                       ,@SuccessGuarantee
                                       ,@SpecialRequirement
                                       ,@Technology 
                                       ,@Frequency 
                                       ,@Miscelleneous
                                       ,@StatusID
                                       ,@CreationDate
                                       ,NULL
                                       ,@Priority)";

            data.AddSqlParameter("UseCaseID", pUseCaseID, SqlDbType.UniqueIdentifier);
            data.AddSqlParameter("Name", pUseCaseName, SqlDbType.VarChar);
            data.AddSqlParameter("Scope", pScope, SqlDbType.VarChar);
            data.AddSqlParameter("Level", pLevel, SqlDbType.VarChar);
            data.AddSqlParameter("PrimaryActor", pPrimaryActor, SqlDbType.VarChar);
            data.AddSqlParameter("StakeHolders", pStakeHolders, SqlDbType.VarChar);
            data.AddSqlParameter("Precondition", pPrecondition, SqlDbType.VarChar);
            data.AddSqlParameter("SuccessGuarantee", pSuccess, SqlDbType.VarChar);
            data.AddSqlParameter("SpecialRequirement", pSpecilaRequirements, SqlDbType.VarChar);
            data.AddSqlParameter("Technology", pTechnology, SqlDbType.VarChar);
            data.AddSqlParameter("Frequency", pFrequency, SqlDbType.VarChar);
            data.AddSqlParameter("Miscelleneous", pMisc, SqlDbType.VarChar);
            data.AddSqlParameter("StatusID", pStatusID, SqlDbType.Int);
            data.AddSqlParameter("CreationDate", pCreationDate, SqlDbType.DateTime);
            data.AddSqlParameter("Priority", pPriority, SqlDbType.VarChar);
            data.ExecuteStatement(sqlText);

            new MainSuccessBS().CreateMSS(pProjectID, pUseCaseID, dtMss);
            new ExtentionBS().CreateExtentions(pProjectID, pUseCaseID, dtExtention);
            new ExtentionStepBS().CreateExtentionSteps(dtExtentionSteps);
            
        }

        public void UpdateUseCase(Guid pProjectID, Guid pUseCaseID)
        {
            IData data = GetDataObject();
            string sqlText = @"BEGIN TRAN;

                                WITH MSS AS 
                                (
	                                SELECT DISTINCT 
		                                [MssID]
	                                FROM [SemiCaseDB].[dbo].[RELATION]
	                                WHERE ProjectID = @ProjectID AND UseCaseID = @UseCaseID AND TestCaseID IS NULL
                                )

                                DELETE FROM [SemiCaseDB].[dbo].[MSS]
                                WHERE MssID IN (SELECT * FROM MSS)

                                DELETE FROM [SemiCaseDB].[dbo].[EXTENTION_STEP]
                                WHERE ExtentionID IN (SELECT DISTINCT 
															[ExtentionID]
														FROM [SemiCaseDB].[dbo].[RELATION]
														WHERE ProjectID = @ProjectID AND UseCaseID = @UseCaseID)

                                DELETE FROM [SemiCaseDB].[dbo].[EXTENTION]
                                WHERE ExtentionID IN (SELECT DISTINCT 
															[ExtentionID]
														FROM [SemiCaseDB].[dbo].[RELATION]
														WHERE ProjectID = @ProjectID AND UseCaseID = @UseCaseID)

                                DELETE FROM [SemiCaseDB].[dbo].[RELATION]
                                WHERE RelationID IN ( SELECT DISTINCT 
															RelationID
														FROM [SemiCaseDB].[dbo].[RELATION]
														WHERE ProjectID = @ProjectID AND UseCaseID = @UseCaseID AND TestCaseID IS NULL)

                                COMMIT TRAN;";

            data.AddSqlParameter("ProjectID", pProjectID, SqlDbType.UniqueIdentifier);
            data.AddSqlParameter("UseCaseID", pUseCaseID, SqlDbType.UniqueIdentifier);

            data.ExecuteStatement(sqlText);
        }

        public void DeleteUseCase(Guid pProjectID, Guid pUseCaseID)
        {
            IData data = GetDataObject();
            string sqlText = @"BEGIN TRAN;

                                WITH MSS AS 
                                (
	                                SELECT DISTINCT 
		                                [MssID]
	                                FROM [SemiCaseDB].[dbo].[RELATION]
	                                WHERE ProjectID = @ProjectID AND UseCaseID = @UseCaseID
                                )

                                DELETE FROM [SemiCaseDB].[dbo].[MSS]
                                WHERE MssID IN (SELECT * FROM MSS)

                                DELETE FROM [SemiCaseDB].[dbo].[EXTENTION]
                                WHERE ExtentionID IN (SELECT DISTINCT 
															[ExtentionID]
														FROM [SemiCaseDB].[dbo].[RELATION]
														WHERE ProjectID = @ProjectID AND UseCaseID = @UseCaseID)

                                DELETE FROM [SemiCaseDB].[dbo].[EXTENTION_STEP]
                                WHERE ExtentionID IN (SELECT DISTINCT 
															[ExtentionID]
														FROM [SemiCaseDB].[dbo].[RELATION]
														WHERE ProjectID = @ProjectID AND UseCaseID = @UseCaseID)

                                DELETE FROM [SemiCaseDB].[dbo].[RELATION]
                                WHERE RelationID IN ( SELECT DISTINCT 
															RelationID
														FROM [SemiCaseDB].[dbo].[RELATION]
														WHERE ProjectID = @ProjectID AND UseCaseID = @UseCaseID)

                                DELETE FROM [SemiCaseDB].[dbo].[USE_CASE]
                                WHERE UseCaseID = @UseCaseID

                                COMMIT TRAN;";

            data.AddSqlParameter("ProjectID", pProjectID, SqlDbType.UniqueIdentifier);
            data.AddSqlParameter("UseCaseID", pUseCaseID, SqlDbType.UniqueIdentifier);

            data.ExecuteStatement(sqlText);
        }

        public void CreateNonFunctionalUseCase(Guid pProjectID, Guid pUseCaseID, string pName, int pStatusID)
        {
            IData data = GetDataObject();
            string sqlText = @"INSERT INTO [SemiCaseDB].[dbo].[USE_CASE]
                                   ([UseCaseID]
                                   ,[Name]
                                   ,[CreationDate]
                                   ,[Nonfunctional]
                                   ,StatusID)
                             VALUES
                                   (@UseCaseID
                                   ,@Name
                                   ,@CreationDate
                                   ,@Nonfunctional
                                   ,@StatusID);

                            INSERT INTO [SemiCaseDB].[dbo].[RELATION]
                                       ([RelationID]
                                       ,[ProjectID]
                                       ,[UseCaseID])
                                 VALUES
                                       (NEWID()
                                       ,@ProjectID
                                       ,@UseCaseID)";

            data.AddSqlParameter("ProjectID", pProjectID, SqlDbType.UniqueIdentifier);
            data.AddSqlParameter("UseCaseID", pUseCaseID, SqlDbType.UniqueIdentifier);
            data.AddSqlParameter("Nonfunctional", true, SqlDbType.Bit);
            data.AddSqlParameter("Name", pName, SqlDbType.VarChar);
            data.AddSqlParameter("CreationDate", DateTime.Now, SqlDbType.DateTime);
            data.AddSqlParameter("StatusID", pStatusID, SqlDbType.Int);

            data.ExecuteStatement(sqlText);
        }
    }
}
